package com.wanmait.cookbook.dao;
/*
 * 鐢ㄦ埛琛―AO
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.wanmait.cookbook.vo.City;
import com.wanmait.cookbook.vo.Pager;
import com.wanmait.cookbook.vo.Userinfo;
import com.wanmait.demo.util.DBHelper;

public class UserinfoDAO {
	private UserinfoDAO() {}
	private static UserinfoDAO userinfoDAO = new UserinfoDAO();
	public static UserinfoDAO getUserinfoDAO() {
		return userinfoDAO;
	}




	DBHelper dbHelper=DBHelper.getHelper();

	//点击用户查询用户详细信息
	public List<Userinfo> detailUserinfo(String name){
		List<Userinfo >blurryNames=new ArrayList<Userinfo>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Userinfo blurryName=null;
		
		try {
			String sql=" SELECT * FROM " + 
					" (SELECT * FROM userinfo WHERE userinfo.name like ?) AS temp ";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			
			rs=ps.executeQuery();
			while(rs.next()) {
				blurryName=new Userinfo();
				blurryName.setId(rs.getInt("id"));
				blurryName.setAvatar(rs.getString("avatar"));
				blurryName.setInfo(rs.getString("info"));
				blurryName.setName(rs.getString("name"));
				blurryNames.add(blurryName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		
	return blurryNames;
	}
	
	
	
	
	//模糊查询用户行数
	public int getBlurryRows(String name) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT COUNT(*) AS c FROM  " );
		sb.append(" (SELECT * FROM userinfo WHERE userinfo.name like ?) ");
		sb.append(" AS temp ");
		sb.append("  WHERE login_prohibited=0 AND ENABLE=1 ");
		
		String sql = sb.toString();
		try 
		{
			conn = DBHelper.getHelper().getConn();
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			rs = ps.executeQuery();
			
			rs.next();//移到第一行
			
			int rows = rs.getInt("c");
			
			return rows;
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();//输出异常的信息
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		
		return 0;//出现异常
	}
	
	//用户昵称模糊查询所有用户
	public List<Userinfo> blurryName(String name,Pager pager){
		List<Userinfo >blurryNames=new ArrayList<Userinfo>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Userinfo blurryName=null;
		
		try {
			String sql=" SELECT * FROM " + 
					" (SELECT * FROM userinfo WHERE userinfo.name like ?) AS temp " + 
					" WHERE login_prohibited=0 AND ENABLE=1 "+
					" LIMIT ?,? ";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			ps.setInt(2, pager.getStart());
			ps.setInt(3, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				blurryName=new Userinfo();
				blurryName.setId(rs.getInt("id"));
				blurryName.setName(rs.getString("name"));
				blurryName.setAvatar(rs.getString("avatar"));
				blurryName.setInfo(rs.getString("info"));
				blurryNames.add(blurryName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		
	return blurryNames;
	}
	
	//得到所有热门人数，用来确定分页的行数


	public int getHotUserinfoRows() {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="SELECT COUNT(*) AS userinfoRows FROM userinfo WHERE hot=1 AND mute=0 AND login_prohibited=0 AND ENABLE=1";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			int rows=rs.getInt("userinfoRows");
			return rows;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		return 0;
		
	}
	

	//�޸�����
	public void updatePassword(Userinfo userinfo) {
		String sql = "update userinfo set password=? where id=?";
		DBHelper.getHelper().executeUpdate(sql, userinfo.getPassword(),userinfo.getId());
	}
	//�޸�����
	public void updateUserinfo(Userinfo userinfo,int i) {

	    String sql = "update userinfo set name=?,telephone=?,birthday=?,city_id=?,taste=?,info=?,sex=? where id=?";
	    
	    if(userinfo.getAvatar()!="nullnull") {   
	      sql = "update userinfo set name=?,telephone=?,birthday=?,city_id=?,taste=?,info=?,avatar=?,sex=? where id=?";
	      dbHelper.executeUpdate(sql,userinfo.getName(),
	          userinfo.getTelephone(),
	          userinfo.getBirthday(),
	          userinfo.getCity().getId(),
	          userinfo.getTaste(),
	          userinfo.getInfo(),
	          userinfo.getAvatar(),
	          userinfo.getSex(),
	          userinfo.getId()
	          );
	    }else {
	      
	      dbHelper.executeUpdate(sql,userinfo.getName(),
	          userinfo.getTelephone(),
	          userinfo.getBirthday(),
	          userinfo.getCity().getId(),
	          userinfo.getTaste(),
	          userinfo.getInfo(),
	          userinfo.getSex(),
	          userinfo.getId()
	          );
	      
	    }  
	  }
	


	public List<Userinfo> allHotUserinfo(Pager pager){

		List<Userinfo> allUserinfo=new ArrayList<Userinfo>();
		Userinfo userinfo;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT *  FROM userinfo WHERE ENABLE=1 AND mute=0 AND login_prohibited=0 AND hot=1 LIMIT ?,? ";
		try {
			
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				userinfo=new Userinfo();
				userinfo.setAvatar(rs.getString("avatar"));
				userinfo.setId(rs.getInt("id"));
				userinfo.setName(rs.getString("name"));
				userinfo.setTaste(rs.getString("taste"));
				allUserinfo.add(userinfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		return allUserinfo;
		
	}
	
	//鍏虫敞浜�
	public int getrows(String username,String password) {
		Userinfo userinfo=new Userinfo();
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) as c from userinfo,city,attention where userinfo.username =? ");
		sb.append(" and userinfo.password=? and userinfo.city_id =city.id ");
		sb.append(" and userinfo.id=attention.ueserinfo_id ");
		int count=0;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=sb.toString();
		 conn=dbHelper.getConn();
		 try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs=ps.executeQuery();
			rs.next();
			count=rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 dbHelper.closeConn(rs, ps, conn);
		return count;
	}
	//椋熷搧鏀惰棌鏁�
	public int cookbookCollect(String username,String password) {
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) as c from cookbook_collect,userinfo ");
		sb.append(" where userinfo.username=? and userinfo.password=? ");
		sb.append(" and userinfo.id=cookbook_collect.userinfo_id ");
		int count=0;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		conn=dbHelper.getConn();
		String sql=sb.toString();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs=ps.executeQuery();
			rs.next();
			count =rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	//浣滃搧
	public int  workCollect(String username,String password) {
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) as c from works_collect,userinfo ");
		sb.append(" where userinfo.username=? and userinfo.password=? ");
		sb.append(" and userinfo.id=works_collect.userinfo_id ");
		int count=0;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		conn=dbHelper.getConn();
		String sql=sb.toString();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs=ps.executeQuery();
			rs.next();
			count =rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	//鑾峰緱鍏虫敞鏁�
	public int getattention(String username,String password) {
		Userinfo userinfo=new Userinfo();
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) as c from userinfo,attention where userinfo.username ");
		sb.append(" =? and userinfo.password=? and  userinfo.id=attention.attention_ueserinfo_id ");
		int count=0;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=sb.toString();
		 conn=dbHelper.getConn();
		 try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs=ps.executeQuery();
			rs.next();
			count=rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 dbHelper.closeConn(rs, ps, conn);
		return count;
	}


	public Userinfo login(String username,String password) {
		Userinfo userinfo=new Userinfo();
		StringBuilder sb=new StringBuilder();
		sb.append(" select * from userinfo,city where userinfo.username ");
		sb.append(" =? and userinfo.password=? and userinfo.city_id ");
		sb.append(" =city.id ");
		City city=new City();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=sb.toString();
		 conn=dbHelper.getConn();
		 try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs=ps.executeQuery();
			while(rs.next()) {
	
				city.setId(rs.getInt("city_id"));
				city.setName(rs.getString("city.name"));
				userinfo.setId(rs.getInt("id"));
				userinfo.setUsername(rs.getString("username"));
				userinfo.setPassword(rs.getString("password"));
				userinfo.setTelephone(rs.getString("telephone"));
				userinfo.setName(rs.getString("name"));
				userinfo.setBirthday(rs.getTimestamp("birthday"));
				userinfo.setSex(rs.getBoolean("sex"));
				userinfo.setCity(city);
				userinfo.setTaste(rs.getString("taste"));
				userinfo.setAvatar(rs.getString("avatar"));
				userinfo.setInfo(rs.getString("info"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 dbHelper.closeConn(rs, ps, conn);
		return userinfo;
	}
	
	//****************************************lishuo
	//后台获得所有用户的总记录数；
		public int getAllUserinfoRows() {
			Connection conn = null;
			PreparedStatement ps =null;
			ResultSet rs = null;
			String sql = "SELECT count(*) c FROM userinfo WHERE ENABLE=1";
			try {
				conn = DBHelper.getHelper().getConn();	
				ps = conn.prepareStatement(sql);		
				rs = ps.executeQuery();		
				rs.next();//移到第一行
				int rows = rs.getInt("c");
				return rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return 0;
		}
		
		//后台获得用户  分页
		public List<Userinfo> getUserinfos(Pager pager) {
			List<Userinfo> userinfos = new ArrayList<>();
			Connection conn =null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql = "SELECT * FROM userinfo WHERE ENABLE=1 LIMIT ?,?";
			try {
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, pager.getStart());
				ps.setInt(2, pager.getPageSize());
				rs=ps.executeQuery();
				Userinfo userinfo = null;
				while(rs.next()) {
					userinfo = new Userinfo();
					userinfo.setAvatar(rs.getString("avatar"));
					userinfo.setHot(rs.getBoolean("hot"));
					userinfo.setUsername(rs.getString("username"));
					userinfo.setInputTime(rs.getTimestamp("input_time"));
					userinfo.setEnable(true);
					userinfo.setId(rs.getInt("id"));
					userinfo.setLoginProhibited(rs.getBoolean("login_prohibited"));
					userinfo.setMute(rs.getBoolean("mute"));
					userinfos.add(userinfo);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return userinfos;
		}
		
		//通过用户id设置用户热门
		public void setUserinfoHotByUserinfoId(int id,int val) {
			String sql="update userinfo set hot=? where id=?";
			DBHelper.getHelper().executeUpdate(sql,val,id);
		}
		//通过用户id设置用户热门
		public void setUserinfoMuteByUserinfoId(int id,int val) {
			String sql="update userinfo set mute=? where id=?";
			DBHelper.getHelper().executeUpdate(sql,val,id);
		}
		//通过用户id设置用户热门
		public void setUserinfoBanByUserinfoId(int id,int val) {
			String sql="update userinfo set login_prohibited=? where id=?";
			DBHelper.getHelper().executeUpdate(sql,val,id);
		}
		
		
		// 后台通过搜索关键字获得所有用户的总记录数；
		public int getUserinfoRowsByInput(String val) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql = "SELECT count(*) c FROM userinfo WHERE ENABLE=1 AND userinfo.username like ? ";
			try {
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				rs = ps.executeQuery();
				rs.next();// 移到第一行
				int rows = rs.getInt("c");
				return rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return 0;
		}
		
		// 后台通过搜索关键字获得用户 分页
		public List<Userinfo> getUserinfosByInput(Pager pager,String val) {
			List<Userinfo> userinfos = new ArrayList<>();
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql = "SELECT * FROM userinfo WHERE ENABLE=1 and username like ? LIMIT ?,?";
			try {
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				ps.setInt(2, pager.getStart());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				Userinfo userinfo = null;
				while (rs.next()) {
					userinfo = new Userinfo();
					userinfo.setAvatar(rs.getString("avatar"));
					userinfo.setHot(rs.getBoolean("hot"));
					userinfo.setUsername(rs.getString("username"));
					userinfo.setInputTime(rs.getTimestamp("input_time"));
					userinfo.setEnable(true);
					userinfo.setId(rs.getInt("id"));
					userinfo.setLoginProhibited(rs.getBoolean("login_prohibited"));
					userinfo.setMute(rs.getBoolean("mute"));
					userinfos.add(userinfo);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return userinfos;
		}
		
		// 通过ID设置禁言
		public void setMuteByid(int id) {
			String sql = "update userinfo set mute=1 where id=?";
			DBHelper.getHelper().executeUpdate(sql, id);
		}
		
		// 通过IDs禁言多个用户
		public void setMutesByIds(String ids) {
			String sql = "update userinfo set mute=1 where id in(" + ids + ")";
			DBHelper.getHelper().executeUpdate(sql);
		}
		//时间段新增用户数量
		public int newUserYesterdayRows(String y,String d) {
			Connection conn = null;
			PreparedStatement ps =null;
			ResultSet rs = null;
			String sql = "select date(input_time) d, count(1) c from userinfo " + 
					" where input_time >= ? and input_time <= ? " + 
					" group by d order by d DESC ";
			try {
				conn = DBHelper.getHelper().getConn();	
				ps = conn.prepareStatement(sql);
				ps.setString(1, y);
				ps.setString(2, d);
				rs = ps.executeQuery();	
				int sum=0;
				while(rs.next())
				{
					int rows = rs.getInt("c");
					sum +=rows;
				}
				return sum;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return 0;
		}
		// 通过用户名和密码查找用户
		public Userinfo findByUsernameAndPassword(String username,String password) {
			
			Userinfo userinfo = null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs=null;
			
			String sql ="select * from userinfo where username=? and password=? and login_prohibited=0 and enable=1";
			
			try {

				conn = DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setString(1,username);
				ps.setString(2,password);
				rs=ps.executeQuery();
				
				if(rs.next()) {
					userinfo = new Userinfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setUsername(username);
					userinfo.setPassword(password);
					userinfo.setName(rs.getString("name"));
					userinfo.setBirthday(rs.getTimestamp("birthday"));
					userinfo.setInputTime(rs.getTimestamp("input_time"));
					userinfo.setAvatar(rs.getString("avatar"));
					userinfo.setEnable(true);
					City city = new City();
					city.setId(rs.getInt("city_id"));
					userinfo.setCity(city);
					userinfo.setHot(rs.getBoolean("hot"));
					userinfo.setInfo(rs.getString("info"));
					userinfo.setLoginProhibited(false);
					userinfo.setMute(rs.getBoolean("mute"));
					userinfo.setSex(rs.getBoolean("sex"));
					userinfo.setTaste(rs.getString("taste"));
					userinfo.setTelephone(rs.getString("telephone"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {

				DBHelper.getHelper().closeConn(rs, ps, conn);
			}	
			return userinfo;		
		}
		

		 /**
		   *  注册判断用户名
		   * @param username
		   * @return
		   */
		  public Boolean judgeUsername(String username) {
		    Connection conn = null;
		    PreparedStatement ps = null;
		    ResultSet rs = null;
		    String sql = "SELECT * FROM userinfo WHERE username=?";
		    try {
		      conn = DBHelper.getHelper().getConn();
		      ps = conn.prepareStatement(sql);
		      ps.setString(1, username);
		      rs  = ps.executeQuery();
		      return rs.next();
		    } catch (SQLException e) {
		      // TODO Auto-generated catch block
		      e.printStackTrace();
		    }
		    finally {
		      DBHelper.getHelper().closeConn(rs, ps, conn);
		    }
		    return false;
		  }
		  
		  /**
		   *  注册
		   * @param userinfo
		   */
		  public void registe(Userinfo userinfo) {
		    String sql = "insert into userinfo(username,password,name,taste,avatar) values(?,?,?,?,?)";
		    DBHelper.getHelper().executeUpdate(sql, userinfo.getUsername(),userinfo.getPassword(),userinfo.getName(),userinfo.getTaste(),userinfo.getAvatar());
		  }

		
		
		public Userinfo getUserinfoByUserinfoId(int id) {
			Userinfo userinfo = null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs=null;
			
			String sql ="select * from userinfo,city where userinfo.id=? and userinfo.city_id=city.id and enable=1";
			
			try {

				conn = DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1,id);
				rs=ps.executeQuery();
				
				if(rs.next()) {
					userinfo = new Userinfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setUsername(rs.getString("userinfo.username"));
					userinfo.setName(rs.getString("name"));
					userinfo.setBirthday(rs.getTimestamp("birthday"));
					userinfo.setInputTime(rs.getTimestamp("input_time"));
					userinfo.setAvatar(rs.getString("avatar"));
					userinfo.setEnable(true);
					userinfo.setHot(rs.getBoolean("hot"));
					userinfo.setInfo(rs.getString("info"));
					userinfo.setLoginProhibited(false);
					userinfo.setMute(rs.getBoolean("mute"));
					userinfo.setSex(rs.getBoolean("sex"));
					userinfo.setTaste(rs.getString("taste"));
					userinfo.setTelephone(rs.getString("telephone"));
					City city = new City();
					city.setId(rs.getInt("city.id"));
					city.setName(rs.getString("city.name"));
					userinfo.setCity(city);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {

				DBHelper.getHelper().closeConn(rs, ps, conn);
			}	
			return userinfo;
		}

}
